#!/usr/bin/env python
#coding=utf-8

import re
import os
import time
import click
import logging

from vic.exchange.vic_huobi.huobi_http import HuobiHttp
from vic.exchange.vic_okex.okex_http import OkexHttp
from vic.exchange.vic_binance.binance_http import BinaHttp
from vic.exchange.vic_okex.okexft_http import OkexftHttp

from lib.common import excel_folder_path, format_timestamp
from lib.xsl import XLSWriter
from lib.bina_risk_netvalue import BinanService
from lib.huobi_risk_netvalue import HuobiService
from lib.okex_risk_netvalue import OkexService
from lib.mysql_pool import MysqlPool

mysql = MysqlPool('localhost', 8800, 'ops', 'ops!@#9988', 'vic', 'utf8mb4')

def bian_accounts():
    """币安账户"""
    url       = "https://api.binance.com"
    apiKey    = 'SD8Fs5yvz0SYR9XyDeiYPoU1FbZvPXFYodih7QZZ9Wc9aBWBSvpNC1Ei9rMso5uD'
    apiSecret = '3h4BByt92BpwvaYVduiDvsm5iygFlszp20EsGxhzW75bvahdyr6ndgFFDNkIyfbT'

    bs = BinanService(url, apiKey, apiSecret)
    currency_list = bs.currency_list()
    trading = u'币安'
    current_time = time.strftime('%Y-%m-%d %H:%M',time.localtime())
    trading_list, balance_list, usdt_list, time_list = [], [], [], []

    for currency in currency_list:
        usdt    = bs.currency_tousdt(currency)
        balance = bs.currency_balance(currency)

        balance_list.append(balance)
        trading_list.append(trading)
        time_list.append(current_time)
        usdt_list.append(usdt)


    bina_list = zip(currency_list,
                     trading_list,
                     balance_list,
                     usdt_list,
                     time_list)

    return bina_list


def okex_accounts():
    """Okex账户"""

    url       = "https://www.okex.com/api/v1"
    apiKey = '05b9ad66-c12b-433a-88a0-a123a7ac4826'
    apiSecret = '0EC801280A1C1B63171C346DE6A47F38'


    okex_http   = OkexHttp(url, apiKey, apiSecret)
    okexft_http = OkexftHttp(url, apiKey, apiSecret)
    funds       = okex_http.userinfo()['info']['funds']
    future_userinfo = okexft_http.future_userinfo()['info']

    freezed = funds['freezed']
    borrow  = funds['borrow']
    free    = funds['free']
    coin_list=freezed.keys()

    new_coin_list, trade_net_list, time_list = [], [], []
    balance_list, to_usdt_list, source_list  = [], [], []
    for coin in coin_list:

        userinfo       = future_userinfo.get(coin)  # 全仓信息
        account_rights = float(userinfo.get('account_rights')) if userinfo else 0.0 # 账户权益

        free    = funds['free'][coin] if funds['free'].get(coin) else 0
        freezed = funds['freezed'][coin] if funds['freezed'].get(coin) else 0

        balance = float(free + freezed) + account_rights

        if balance <= 0:
            continue

        print('coin:%s, free:%s, freezed:%s, balance:%s, account_rights:%s' % (coin, free, freezed, balance, account_rights))

        if coin=='usdt':
            tousdt_rate = 1
        else:
            p = okex_http.ticker(coin+'_usdt')
            if p.get('error_code') == 1007:
                tousdt_rate = 0
            else:
                tousdt_rate = float(p['ticker']['last'])
        print('### tousdt_rate:%s'% tousdt_rate)

        free_usdt           = float(free)*tousdt_rate
        freezed_usdt        = float(freezed)*tousdt_rate
        account_rights_usdt = float(account_rights)*tousdt_rate
        free_usdt           = float(free)*tousdt_rate
        usdt                = float(balance)*tousdt_rate

        current_time = time.strftime('%Y-%m-%d %H:%M',time.localtime())
        if account_rights > 0:
            new_coin_list.append(coin.upper())
            trade_net_list.append('Okex')
            balance_list.append(account_rights)
            to_usdt_list.append(account_rights_usdt)
            time_list.append(current_time)
            source_list.append(u'合约')

        if (free+freezed) > 0:
            new_coin_list.append(coin.upper())
            trade_net_list.append('Okex')
            balance_list.append(free+freezed)
            to_usdt_list.append(free_usdt + freezed_usdt)
            time_list.append(current_time)
            source_list.append(u'币币')

    okex_list = zip(new_coin_list, trade_net_list, balance_list, to_usdt_list, time_list, source_list)
    return okex_list



def huo_accounts():
    """火币账户"""

    url        = "https://api.huobipro.com"
    apiKey     = "bced7658-c1a43d38-c793c186-97b7b"
    apiSecret  = "398d3adb-014169bc-9e618431-64888"

    huobi_http = HuobiHttp(url, apiKey, apiSecret)
    accounts=huobi_http.accounts()

    huobi_list, data_list = [], []
    for a in accounts['data']:
        data=huobi_http.balance(a['id'])['data']
        currency_list, time_list, balance_list, to_usdt_list, trade_net_list = [], [], [], [], []
        for data in data['list']:
            if float(data['balance'])>0:
                currency = data['currency'].upper()
                if currency == 'USDT' and currency in currency_list:
                    continue
                currency_list.append(currency)
                time_list.append(time.strftime('%Y-%m-%d %H:%M',time.localtime()))
                balance_list.append('%.8f' % float(data['balance']))
                trade_net_list.append('火币')

                if currency !='USDT':
                    tt=data['currency']+'usdt'
                    ww=huobi_http.detail_merged(tt)['tick']['ask'][0]
                    to_usdt_list.append('%.8f' % float(float(data['balance'])*ww))
                else:
                    to_usdt_list.append('%.8f' % float(data['balance']))

        data_list = zip(currency_list, trade_net_list, balance_list, to_usdt_list, time_list)
        huobi_list.extend(data_list)

    return huobi_list


def collect_accounts(coin_list, bian_list, okex_list, huobi_list):
    """ 账户汇总 """

    collect_list = []
    for coin in coin_list:
        data_list = []
        bian_balance, bian_to_usdt, okex_balance, okex_to_usdt, huo_balance, huo_to_usdt = 0, 0, 0, 0, 0, 0
        for b in bian_list:
            if b[0] == coin:
                bian_balance += float(b[2]) # 持仓数量
                bian_to_usdt += float(b[3]) # 折合usdt

        for o in okex_list:
            if o[0] == coin:
                okex_balance += float(o[2]) # 持仓数量
                okex_to_usdt += float(o[3]) # 折合usdt

        for h in huobi_list:
            if h[0] == coin:
                huo_balance += float(h[2]) # 持仓数量
                huo_to_usdt += float(h[3]) # 折合usdt

        sum_balance = float(bian_balance) + float(okex_balance) + float(huo_balance)
        sum_to_usdt = float(bian_to_usdt) + float(okex_to_usdt) + float(huo_to_usdt)

        #print('sum_balance:%s'% sum_balance)
        #print('sum_to_usdt:%s'% sum_to_usdt)

        if sum_balance <= 0:
            continue

        data_list.append(coin.upper())
        data_list.append(sum_balance)
        data_list.append(sum_to_usdt)
        data_list.append(time.strftime('%Y-%m-%d %H:%M',time.localtime()))
        collect_list.append(data_list)

    file_path = u'%s/' % excel_folder_path()
    sheet_name = u'账户汇总列表.xls'
    file = file_path + sheet_name
    xlswriter  = XLSWriter(file=file.encode('utf8'))


    bian_sum_usdt, huobi_sum_usdt, okex_sum_usdt = 0, 0, 0
    export_title = [u'币种', u'交易所', u'持仓数量', u'折合usdt', u'当前时间', u'', u'', u'',\
                    u'币种', u'交易所', u'持仓数量', u'折合usdt', u'当前时间', u'', u'', u'',\
                    u'币种', u'交易所', u'持仓数量', u'折合usdt', u'当前时间', u'来源']
    xlswriter.writerow(export_title, sheet_name=sheet_name)

    for i in range(max(len(bian_list), len(huobi_list), len(okex_list))):
        export_data = []
        bian  = bian_list[i] if len(bian_list) > i else ''
        huo   = huobi_list[i] if len(huobi_list) > i else ''
        okex  = okex_list[i] if len(okex_list) > i else ''

        # 币安
        if bian:
            bian_sum_usdt += float(bian[3])
            export_data += [bian[0], bian[1], bian[2], bian[3], bian[4], u'', u'', u'']
        else:
            export_data += [u'', u'', u'', u'', u'', u'', u'', u'']

        # 火币
        if huo:
            huobi_sum_usdt += float(huo[3])
            export_data += [huo[0], huo[1], huo[2], huo[3], huo[4], u'', u'', u'']
        else:
            export_data += [u'', u'', u'', u'', u'', u'', u'', u'']

        # okex
        if okex:
            okex_sum_usdt += float(okex[3])
            print ('coin:%s, okex_sum_usdt:%s'% (okex[0], okex_sum_usdt))
            export_data += [okex[0], okex[1], okex[2], '%.8f' % okex[3], okex[4], okex[5]]
        else:
            export_data += [u'', u'', u'', u'', u'', u'', u'', u'']

        xlswriter.writerow(export_data, sheet_name=sheet_name)

    xlswriter.writerow([], sheet_name=sheet_name)  #写一空行，用于隔开总usdt
    xlswriter.writerow([u' 合计:', bian_sum_usdt, u'', u'', u'', u'', u'', u'',\
                        u' 合计:', huobi_sum_usdt, u'', u'', u'', u'', u'', u'',\
                        u' 合计:', okex_sum_usdt], sheet_name=sheet_name)
    xlswriter.writerow([], sheet_name=sheet_name)  #写一空行，用于隔开汇总表
    xlswriter.writerow([], sheet_name=sheet_name)  #写一空行，用于隔开汇总表

    # 汇总
    export_title = [u'币种', u'交易所', u'持仓数量', u'折合usdt', u'当前时间']
    xlswriter.writerow(export_title, sheet_name=sheet_name)
    sum_usdt = bian_sum_usdt + huobi_sum_usdt + okex_sum_usdt
    for collect in collect_list:
        export_data = [collect[0], u'汇总',collect[1], collect[2], collect[3]]
        xlswriter.writerow(export_data, sheet_name=sheet_name)
        logging.info(collect)

    xlswriter.writerow([], sheet_name=sheet_name)  #写一空行，用于隔开总usdt
    xlswriter.writerow([u' 合计:', sum_usdt], sheet_name=sheet_name)
    xlswriter.save()

    return collect_list


def collect_bat(file_name):
    """汇总脚本"""


    url       = "https://api.binance.com"
    apiKey    = 'SD8Fs5yvz0SYR9XyDeiYPoU1FbZvPXFYodih7QZZ9Wc9aBWBSvpNC1Ei9rMso5uD'
    apiSecret = '3h4BByt92BpwvaYVduiDvsm5iygFlszp20EsGxhzW75bvahdyr6ndgFFDNkIyfbT'
    bs = BinanService(url, apiKey, apiSecret)

    url       = "https://www.okex.com/api/v1"
    apiKey    = '05b9ad66-c12b-433a-88a0-a123a7ac4826'
    apiSecret = '0EC801280A1C1B63171C346DE6A47F38'
    ok = OkexService(url, apiKey, apiSecret)

    url        = "https://api.huobipro.com"
    apiKey     = "bced7658-c1a43d38-c793c186-97b7b"
    apiSecret  = "398d3adb-014169bc-9e618431-64888"
    hs = HuobiService(url, apiKey, apiSecret)

    # 读取文件获取套保的usdt
    taobao_usdt , start_amount = 0, 0
    file  = '%s'% file_name
    with open(file, 'r') as f:
        data      = f.read()
        data      = data.replace('\r', '')
        data_list = data.split('\n')
        for d in data_list:
            d_list = re.split(r'[\s\,]+', d)
            key   = d_list[0].upper()
            if key not in ('SYMBOL', 'STARTAMOUNT') and len(d_list) == 2:
                value = float(d_list[1])
                tousdt_rate = ok.tousdt_rate(key)
                usdt = value*tousdt_rate
                taobao_usdt += usdt

            if key == 'STARTAMOUNT':
                start_amount = float(d_list[1])

    bina_usdt  = bs.sum_usdt()
    okex_usdt  = ok.sum_usdt()
    huobi_usdt = hs.sum_usdt()
    sum_usdt   = bina_usdt + okex_usdt + huobi_usdt
    sum_usdt   += taobao_usdt

    net_value = sum_usdt/start_amount
    current_time = time.strftime('%Y-%m-%d %H:%M',time.localtime())
    msg = u'%s   BW资产量化1号基金   最新净值 : %s  最新权益 : %s $' % (current_time, round(net_value, 4), round(sum_usdt, 2))

    sql = 'INSERT INTO `accounts_collect` (`trading`, `is_taobao`, `currency`, `net_value`, `usdt`, `desc`, `add_time`) values (%s, %s, %s, %s, %s, %s, %s)'
    value = (0, 0, u'', net_value, sum_usdt, msg, int(time.time()))
    mysql.insertOne(sql, value)
    mysql.dispose()

    return msg



def export_net_value():
    """导出净值列表"""

    sql = 'select*from accounts_collect'
    net_value_list = mysql.getMany(sql, 2000)

    file_path = u'%s/' % excel_folder_path()
    sheet_name = u'净值汇总列表.xls'
    file = file_path + sheet_name
    xlswriter  = XLSWriter(file=file.encode('utf8'))

    export_title = [u'净值', u'权益', u'添加时间']
    xlswriter.writerow(export_title, sheet_name=sheet_name)
    for nv in net_value_list:
        add_time = format_timestamp(nv.get('add_time'))
        data = [nv.get('net_value'), nv.get('usdt'), add_time]
        xlswriter.writerow(data, sheet_name=sheet_name)

    xlswriter.save()

    return True


if __name__ == "__main__":
    url       = "https://api.binance.com"
    apiKey    = 'i5gVlrajCWSpQulsTbYZUC1Prp2TvxPxF16jjYnHSdo13LfFAi0fUEAjd0Vu7DME'
    apiSecret = 'OYzRJA6B2gxAkETIg1eDGJWZsf1unz5Rrl1aVewelkB3iNLUsArYWedOtonBNeTe'
    #export_net_value()

    from vic.exchange.vic_binance.binance_http import BinaHttp
    http = BinaHttp(url, apiKey, apiSecret)

    symbol = 'ETHUSDT'
    type = 'LIMIT'
    side = 'BUY'
    quantity = 8
    price = 1

    http.place_order(symbol, quantity, price, type, side)
    print('### http.json:%s'% http.json)
    # symbol, quantity, price,  type, side
    #collect_bat('ValuationInput.txt')



